package com.zz.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.zz.entity.Po;

public interface PoRepository extends JpaRepository<Po,String> {
	//对入库单进行分页
		@Query(value="select p.id,p.product_id,pr.name as prname,p.po_time,p.po_num,p.entry_id,p.status,s.name as gysname from po as p left join product pr on (p.product_id=pr.id) left join supplier s on (s.id=pr.supplier_id)",
			    countQuery = "SELECT count(*) FROM po",
				nativeQuery = true)
		public Page<Object[]> showAllPolist(Pageable pageable);
		
		@Query(value="select p.id,p.product_id,pr.name as prname,p.po_time,p.po_num,p.entry_id,p.status,s.name as gysname from po as p left join product pr on (p.product_id=pr.id) left join supplier s on (s.id=pr.supplier_id) where date_sub(CURDATE(),INTERVAL 7 DAY) <= DATE(p.po_time)",
			    countQuery = "SELECT count(*) FROM po where date_sub(CURDATE(),INTERVAL 7 DAY) <= DATE(po_time)",
				nativeQuery = true)
		public Page<Object[]> showPolistBy7Day(Pageable pageable);
		@Query(value="select p.id,p.product_id,pr.name as prname,p.po_time,p.po_num,p.entry_id,p.status,s.name as gysname from po as p left join product pr on (p.product_id=pr.id) left join supplier s on (s.id=pr.supplier_id) where date_sub(CURDATE(),INTERVAL 30 DAY) <= DATE(p.po_time)",
			    countQuery = "SELECT count(*) FROM po where date_sub(CURDATE(),INTERVAL 30 DAY) <= DATE(po_time)",
				nativeQuery = true)
		public Page<Object[]> showPolistBy30Day(Pageable pageable);
		@Query(value="select p.id,p.product_id,pr.name as prname,p.po_time,p.po_num,p.entry_id,p.status,s.name as gysname from po as p left join product pr on (p.product_id=pr.id) left join supplier s on (s.id=pr.supplier_id) where p.po_time between ?1 and ?2",
			    countQuery = "SELECT count(*) FROM po where po_time between ?1 and ?2",
				nativeQuery = true)
		public Page<Object[]> showPolistBytime(String starttime,String lasttime,Pageable pageable);
		
		@Query(value="select * from po where id=?",
				nativeQuery = true)
		public List showPolistByid(String id);
		
		
}
